﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;

namespace PlateNumber {
    public class ProxyServer {
        public int Id { get; set; }
        public string Ip { get; set; }
        public int Port { get; set; }
        public string Country { get; set; }
        public DateTime UpdateDate { get; set; }
        public int Delay { get; set; }
        public ProxyServerSign Sign { get; set; }
        //public DateTime? TodayCanNotBeUse { get; set; }

        public static List<ProxyServer> GetAvailableProxyServers() {
            List<ProxyServer> proxyServers = new List<ProxyServer>();

            using (OleDbConnection conn = new OleDbConnection(Program.ConnectionString)) {
                conn.Open();

                OleDbCommand cmd = new OleDbCommand(
                    "SELECT * FROM ProxyServers WHERE Sign<>@Sign AND ( TodayCanNotBeUse IS NULL OR (TodayCanNotBeUse IS NOT NULL AND DateDiff('d',Date(),TodayCanNotBeUse) > 1) ) ORDER BY Sign DESC, Delay", conn);
                cmd.Parameters.AddWithValue("@Sign", ProxyServerSign.Invalid);
                OleDbDataReader reader = cmd.ExecuteReader();

                while (reader.Read()) {
                    proxyServers.Add(new ProxyServer() {
                        Id = (int)reader["Id"],
                        Ip = reader["Ip"].ToString(),
                        Port = (int)reader["Port"],
                        Country = reader["Country"].ToString(),
                        UpdateDate = (DateTime)reader["UpdateDate"],
                        Delay = (int)reader["Delay"],
                        Sign = (ProxyServerSign)reader["Sign"]
                    });
                }

                reader.Close();
            }

            return proxyServers;
        }

        public static bool Save(ProxyServer proxyServer) {
            using (OleDbConnection conn = new OleDbConnection(Program.ConnectionString)) {
                conn.Open();

                OleDbCommand cmd = null;
                if (proxyServer.Id == 0) {
                    // 添加
                    cmd = new OleDbCommand("SELECT COUNT(*) FROM ProxyServers WHERE Ip=@Ip AND Port=@Port", conn);
                    cmd.Parameters.AddWithValue("@Ip", proxyServer.Ip);
                    cmd.Parameters.AddWithValue("@Port", proxyServer.Port);
                    int count = (int)cmd.ExecuteScalar();

                    if (count == 0) { 
                        cmd = new OleDbCommand("INSERT INTO ProxyServers(Ip, Port, Country, UpdateDate, Delay, Sign) VALUES(@Ip, @Port, @Country, @UpdateDate, @Delay, @Sign)", conn);
                        cmd.Parameters.AddWithValue("@Ip", proxyServer.Ip);
                        cmd.Parameters.AddWithValue("@Port", proxyServer.Port);
                        cmd.Parameters.AddWithValue("@Country", proxyServer.Country);
                        cmd.Parameters.AddWithValue("@UpdateDate", proxyServer.UpdateDate);
                        cmd.Parameters.AddWithValue("@Delay", proxyServer.Delay);
                        cmd.Parameters.AddWithValue("@Sign", (int)proxyServer.Sign);
                        return cmd.ExecuteNonQuery() > 0;
                    }
                }
                else {
                    cmd = new OleDbCommand("UPDATE ProxyServers SET Ip=@Ip, Port=@Port, Country=@Country, UpdateDate=@UpdateDate, Delay=@Delay, Sign=@Sign WHERE Id=@Id", conn);
                    cmd.Parameters.AddWithValue("@Ip", proxyServer.Ip);
                    cmd.Parameters.AddWithValue("@Port", proxyServer.Port);
                    cmd.Parameters.AddWithValue("@Country", proxyServer.Country);
                    cmd.Parameters.AddWithValue("@UpdateDate", proxyServer.UpdateDate);
                    cmd.Parameters.AddWithValue("@Delay", proxyServer.Delay);
                    cmd.Parameters.AddWithValue("@Sign", (int)proxyServer.Sign);
                    cmd.Parameters.AddWithValue("@Id", proxyServer.Id);
                    return cmd.ExecuteNonQuery() > 0;
                }
            }

            return false;
        }

        public static void Remove(int id) {
            using (OleDbConnection conn = new OleDbConnection(Program.ConnectionString)) {
                conn.Open();

                OleDbCommand cmd = new OleDbCommand("DELETE FROM ProxyServers WHERE Id=@Id", conn);
                cmd.Parameters.AddWithValue("@Id", id);
                cmd.ExecuteNonQuery();
            }
        }

        public static void SetSign(int id, ProxyServerSign sign) {
            using (OleDbConnection conn = new OleDbConnection(Program.ConnectionString)) {
                conn.Open();

                OleDbCommand cmd = new OleDbCommand("UPDATE ProxyServers SET Sign=@Sign WHERE Id=@Id", conn);
                cmd.Parameters.AddWithValue("@Sign", (int)sign);
                cmd.Parameters.AddWithValue("@Id", id);
                cmd.ExecuteNonQuery();
            }
        }

        public static ProxyServerSign GetSign(int id) {
            using (OleDbConnection conn = new OleDbConnection(Program.ConnectionString)) {
                conn.Open();

                OleDbCommand cmd = new OleDbCommand("SELECT Sign FROM ProxyServers WHERE Id=@Id", conn);
                cmd.Parameters.AddWithValue("@Id", id);
                int sign = (int)cmd.ExecuteScalar();
                return (ProxyServerSign)sign;
            }
        }

        public static void SetDelay(int id, int delay) {
            using (OleDbConnection conn = new OleDbConnection(Program.ConnectionString)) {
                conn.Open();

                OleDbCommand cmd = new OleDbCommand("UPDATE ProxyServers SET Delay=@Delay WHERE Id=@Id", conn);
                cmd.Parameters.AddWithValue("@Delay", (int)delay);
                cmd.Parameters.AddWithValue("@Id", id);
                cmd.ExecuteNonQuery();
            }
        }

        public static void SetTodayCanNotBeUse(int id) {
            using (OleDbConnection conn = new OleDbConnection(Program.ConnectionString)) {
                conn.Open();

                OleDbCommand cmd = new OleDbCommand("UPDATE ProxyServers SET TodayCanNotBeUse=@TodayCanNotBeUse WHERE Id=@Id", conn);
                cmd.Parameters.AddWithValue("@TodayCanNotBeUse", DateTime.Now.ToShortDateString());
                cmd.Parameters.AddWithValue("@Id", id);
                cmd.ExecuteNonQuery();
            }
        }
    }

    public enum ProxyServerSign {
        Invalid = -1,
        Normal,
        Static
    }
}
